/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.mysql;

import dao.CategoryDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.Category;
import model.Category;

/**
 *
 * @author Student
 */
public class CategoryDAOImpl implements CategoryDAO {

    private Connection conn;

    public CategoryDAOImpl(Connection _conn) {
        this.conn = _conn;
    }

    @Override
    public int save(Category category) throws Exception {
        String sql = "INSERT INTO category(name,description) values(?,?)";
        int out = 0;
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);
            ps.setString(1, category.getName());
            ps.setString(2, category.getDescription());
            out = ps.executeUpdate();
            if (ps != null) {
                ps.close();
            }
        } catch (Exception ex) {
            throw new Exception("Gagal menyimpan Category");
        }
        return out;
    }

    @Override
    public int delete(int id) throws Exception {
        String sql = "DELETE FROM category WHERE id=?";
        int out = 0;
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);
            ps.setInt(1, id);
            out = ps.executeUpdate();
        } catch (Exception ex) {
            throw ex;
        }
        return out;
    }

    @Override
    public int update(Category category) throws Exception {
        String sql = "UPDATE category set name=?,description=? WHERE id=?";
        int out = 0;
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);
            ps.setString(1, category.getName());
            ps.setString(2, category.getDescription());
            ps.setInt(3, category.getId());
            out = ps.executeUpdate();
            if (conn != null) {
                conn.close();
            }
        } catch (Exception ex) {
            throw ex;
        }
        return out;
    }

    @Override
    public Category getCategoryById(int id) throws Exception {
        String sql = "select id,name,description from category where id=?";
        Category cat = null;
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                cat = new Category();
                cat.setId(rs.getInt(1));
                cat.setName(rs.getString(2));
                cat.setDescription(rs.getString(3));
            }
            if (ps != null) {
                ps.close();
            }
        } catch (Exception ex) {
            throw ex;
        }
        return cat;
    }

    @Override
    public List<Category> getAllCategory() throws Exception {
        String sql = "select id,name,description from category";
        List<Category> cats = new ArrayList<Category>();
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);         
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Category cat = new Category();               
                cat.setId(rs.getInt(1));
                cat.setName(rs.getString(2));
                cat.setDescription(rs.getString(3));
                cats.add(cat);
            }
            if (ps != null) {
                ps.close();
            }
        } catch (Exception ex) {
            throw ex;
        }
        return cats;
    }
}
